package com.pub.database.oracle;

import java.util.ArrayList;
import java.util.Hashtable;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Enumeration;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Timestamp;
import com.pub.tools.FormatFunction;

public class OracleFunction {
    public OracleFunction() {
    }

    /**
     * 將多筆資料的SQL轉換為具分頁功能的SQL
     * @param pstrSQL String 原始SQL
     * @param pintMinRow int 此頁的起始列
     * @param pintMaxRow int 此頁的終止列
     * @return String
     */
    public static String incPageSizeSQL(String pstrSQL,
                                        long plngStartRow,
                                        long plngEndRow) {
        StringBuffer lstbPageSizeSQL = new StringBuffer();
        try {
            lstbPageSizeSQL.append("");
            if (pstrSQL.trim().length() > 0) {
                lstbPageSizeSQL.append("select * from ");
                lstbPageSizeSQL.append(
                        " (select a_special.*,rownum as rnm_special from ");
                lstbPageSizeSQL.append(" ( ");
                lstbPageSizeSQL.append(pstrSQL);
                lstbPageSizeSQL.append(" ) a_special ");
                lstbPageSizeSQL.append(" where rownum <= " + plngEndRow + ") ");
                lstbPageSizeSQL.append(" where rnm_special > " + plngStartRow);
            }
        } catch (Exception ex) {
            System.out.println("\nOracleFunction.java" +
                               " method:incPageSizeSQL --> Exception=" +
                               ex.toString());
        }
        return lstbPageSizeSQL.toString();
    }

    /**
     * 將多筆資料的SQL轉換為具分頁功能的SQL
     * @param pstrSQL String 原始SQL
     * @param pintMinRow int 此頁的起始列
     * @param pintMaxRow int 此頁的終止列
     * @return String
     */
    public static String incPageSizeSQLforDV(String[][] pstrFieldsList,
                                             int pintFieldDataIndex,
                                             long plngStartRow,
                                             long plngEndRow,
                                             String pstrTablesList,
                                             String pstrSQLCondition,
                                             String pstrOrderByFields,
                                             String pstrOrderBySeq,
                                             String pstrDVSQLCondition,
                                             String pstrDVOrderByFields,
                                             String pstrDVOrderBySeq
            ) {
        StringBuffer lstbPageSQL = new StringBuffer();
        String lstrOrderByFields = null;
        String lstrOrderBySeq = null;

        lstrOrderByFields = (pstrDVOrderByFields == null ||
                             pstrDVOrderByFields.trim().equals("") ||
                             pstrDVOrderByFields.trim().equals("null") ?
                             pstrOrderByFields : pstrDVOrderByFields);
        lstrOrderBySeq = (pstrDVOrderBySeq == null ||
                          pstrDVOrderBySeq.trim().equals("") ? pstrOrderBySeq :
                          pstrDVOrderBySeq);

        try {
            lstbPageSQL.append("");
            if (pstrFieldsList.length > 0) {
                lstbPageSQL.append("select * from ");
                lstbPageSQL.append(
                        " (select a_special.*,rownum as rnm_special from (select ");
                for (int i = 0; i < pstrFieldsList.length; i++) {
                    lstbPageSQL.append(pstrFieldsList[i][pintFieldDataIndex]);
                    if (!(i == pstrFieldsList.length - 1)) {
                        lstbPageSQL.append(",");
                    }
                }
                lstbPageSQL.append(" from ");
                lstbPageSQL.append(pstrTablesList);

                lstbPageSQL.append(" where ");
                lstbPageSQL.append(pstrSQLCondition != null &&
                                   !pstrSQLCondition.trim().equals("") ?
                                   pstrSQLCondition : "1=1");
                lstbPageSQL.append(pstrDVSQLCondition);

                if(lstrOrderByFields!=null && !lstrOrderByFields.trim().equals("")){
                    lstbPageSQL.append(" order by ");
                    lstbPageSQL.append(lstrOrderByFields);
                    lstbPageSQL.append(" ");
                    lstbPageSQL.append(lstrOrderBySeq);
                }

                lstbPageSQL.append(" ) a_special ");
                lstbPageSQL.append(" where rownum <= " + plngEndRow + ") ");
                lstbPageSQL.append(" where rnm_special >= " + plngStartRow);
            }
        } catch (Exception ex) {
            System.out.println("\nOracleFunction.java" +
                               " method:incPageSizeSQL --> Exception=" +
                               ex.toString());
        }
        return lstbPageSQL.toString();
    }

    /**
     * runStoredProcedure
     *
     * @param pconn Connection
     * @param strProcName String
     * @param strParam String[]
     * @return ArrayList<String>
     * @throws Exception
     * @sample 傳進來的strProcName可以是以下幾種組合
     *         1.strProcName="?=test" strParam.size=1--只有回傳值不用參數
     *         2.strProcName="?=test(in,out)" strParam 有值 --有回傳值並有指定in,out參數
     *         3.strPorcName="test" strParam == null -- 無回傳也無需傳入參數
     *         4.strProcName="test(in,out)" strParam 有值 -- 無回傳值但要傳入參數
     *         5.strProcName="test" strParam 有值 -- 無回傳但要傳入參數(這種情況所有參數都視為in 參數)
     *         6.strPorcName="?=test" strParam.size>1 -- 有回傳值且要傳入傳數(皆視為in 參數)
     */
    @SuppressWarnings("unchecked")
	public ArrayList<String> runStoredProcedure(Connection pconn,String strProcName,
                                        String[] strParam) throws Exception{
        //reference http://my.so-net.net.tw/idealist/Java/JDBC/sp.html
        StringBuffer lstbProcName=new StringBuffer();
        Hashtable<Integer,String> lhtInParam=new Hashtable<Integer,String>();
        Hashtable<Integer,Integer> lhtOutParam=new Hashtable<Integer,Integer>();
        String[] lstrParamInOutList=null;
        int lintStartGetParmIndex=0;

        try{
            //call stord procedure 前後必需加大括弧
            lstbProcName.append("{");

            //呼叫型態為function 有回傳值 sample 1 or 2 or 6
            if(strProcName.indexOf("?=")==0){
                lhtOutParam.put(new Integer(1),new Integer(strParam[0]));
                lintStartGetParmIndex=1;
                lstbProcName.append("?=");
            }

            lstbProcName.append("call ");
            //取得procedure name不含回傳值及括弧,問號
            lstbProcName.append(strProcName.substring((strProcName.indexOf("?=")==0 ? 2 : 0),(strProcName.indexOf("(")>0 ? strProcName.indexOf("(") : strProcName.length())));

            lstbProcName.append("(");

            //需傳入參數 sample 2 or 4
            if(strProcName.indexOf("(")<strProcName.indexOf(")")){
                lstrParamInOutList=strProcName.substring(strProcName.indexOf("(")+1,strProcName.indexOf(")")).split(",");

                for(int i=0;i<lstrParamInOutList.length;i++){
                    if(lstrParamInOutList[i].equals("in")){
                        lhtInParam.put(new Integer(lintStartGetParmIndex),strParam[lintStartGetParmIndex]);
                    }
                    else{
                        lhtOutParam.put(new Integer(lintStartGetParmIndex),new Integer(strParam[lintStartGetParmIndex]));
                    }
                    lintStartGetParmIndex++;
                }
            }
            //sample 1 or 3 or 5 or 6
            else if(strProcName.indexOf("(")<0 && strProcName.indexOf(")")<0 && strParam!=null){
                for(int i=lintStartGetParmIndex;i<strParam.length;i++){
                    lhtInParam.put(new Integer(i+1),strParam[i]);
                }
            }

            if(lhtInParam!=null && lhtInParam.size()!=0){
                lstbProcName.append(FormatFunction.getRepeatStr("?,",lhtInParam.size()));
            }

            if(lhtOutParam!=null && lhtOutParam.size()!=0 && !(lhtOutParam.size()==1 && strProcName.indexOf("?=")==0)){
                lstbProcName.append(FormatFunction.getRepeatStr("?,",lhtOutParam.size()));
            }

            lstbProcName.delete(lstbProcName.toString().length()-1,lstbProcName.toString().length());

            //照理說是要判斷InParam and OutParam的total個數但判斷會很鎖碎，so...這是我想到最快的判斷
            if(lstbProcName.indexOf("?,")>=0 || lstbProcName.indexOf(",?")>=0){
                lstbProcName.append(")");
            }

            lstbProcName.append("}");

            return executeStoredProc(pconn,lstbProcName.toString(),lhtInParam,lhtOutParam);
        }
        catch(Exception e){
            throw new Exception(e);
        }
        finally{
            lstbProcName.delete(0,lstbProcName.length());
            lhtInParam.clear();
            lhtOutParam.clear();

            lstbProcName=null;
            lhtInParam=null;
            lhtOutParam=null;
            lstrParamInOutList=null;
        }
    }

    /* 目的：呼叫proc_flow 的StoreProcedure
       傳入：StoreProcedure名稱、StoreProcedure引數(字串陣列)
       回傳：ArrayList
       範例：ArrayList arlReturn = objQB.flowApprove("wflow.proc_flow",Param); */
    @SuppressWarnings("unchecked")
	public ArrayList<String> callStoreProcedure(Connection pconn, String strProcName,
                                        String[] Param) throws Exception {
        ArrayList arlRST = null;
        try {
            StringBuffer stbTemp = new StringBuffer();
            stbTemp.append("");

            for (int i = 0; i < Param.length; i++) {
                if (i == 0) {
                    stbTemp.append("(?");
                } else {
                    stbTemp.append(",?");
                }
            }

            if (!stbTemp.toString().trim().equals("")) {
                if(Param.length>=1){
                    stbTemp.append(",?)");
                }
                else{
                    stbTemp.append(")");
                }
            }

            String sqlStr = "{call " + strProcName + stbTemp.toString() + "}";

            Hashtable<Integer,String> in = new Hashtable<Integer,String>();
            Hashtable<Integer,Integer> out = new Hashtable<Integer,Integer>();

            for (int i = 1; i <= (Param.length + 1); i++) {
                if (i == (Param.length + 1)) {
                    out.put(new Integer(i), new Integer(java.sql.Types.VARCHAR));
                } else {
                    in.put(new Integer(i), Param[i - 1]);
                }
            }
            arlRST = executeStoredProc(pconn, sqlStr, in, out);
        } catch (Exception e) {
            throw new Exception(e);
        }
        return arlRST;
    } //End - flowApprove

    /**
     * 執行StoreProcedure 可自定 in,out parameter
     *
     * @param conn Connection
     * @param sqlStr String
     * @param inParams Hashtable
     * @param outParams Hashtable
     * @return ArrayList
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
	protected ArrayList executeStoredProc(Connection conn, String sqlStr,
                                          Hashtable<Integer,String> inParams,
                                          Hashtable<Integer,Integer> outParams) throws Exception {
        ArrayList result = new ArrayList();
        CallableStatement aCallStmt = null;
        ResultSet rs = null;

        try {
            aCallStmt = conn.prepareCall(sqlStr);

            if (outParams != null && !outParams.isEmpty()) {
                Object paramObj = null;
                for (Enumeration<Integer> keys = outParams.keys(); keys.hasMoreElements(); ) {
                    paramObj = keys.nextElement();
                    if (paramObj != null) {
                        aCallStmt.registerOutParameter(((Integer) paramObj).
                                intValue(),
                                ((Integer) outParams.get(paramObj)).
                                intValue());
                    }
                } // end of for-loop
            } // end of if outParams.isEmpty
            if (inParams != null && !inParams.isEmpty()) {
                Object paramObj = null;
                Object valueObj = null;
                for (Enumeration<Integer> keys = inParams.keys(); keys.hasMoreElements(); ) {
                    paramObj = keys.nextElement();
                    valueObj = inParams.get(paramObj);
                    if(valueObj == null){
                        aCallStmt.setNull(Integer.parseInt(paramObj.toString()),
                                          java.sql.Types.NULL);

                    }else if (valueObj instanceof String) {
                        aCallStmt.setString(Integer.parseInt("1"),
                                            (String) valueObj);
                    } else if (valueObj instanceof Timestamp) {
                        aCallStmt.setTimestamp(((Integer) paramObj).intValue(),
                                               (Timestamp) valueObj);
                    } else if (valueObj instanceof Integer) {
                        aCallStmt.setInt(((Integer) paramObj).intValue(),
                                         ((Integer) valueObj).intValue());
                    } else if (valueObj instanceof Float) {
                        aCallStmt.setFloat(((Integer) paramObj).intValue(),
                                           ((Float) valueObj).floatValue());
                    } else {
                        aCallStmt.setObject(((Integer) paramObj).intValue(),
                                            valueObj);
                    }
                }
            }
            aCallStmt.execute();
            for (Enumeration<Integer> keys = outParams.keys(); keys.hasMoreElements(); ) {
                Object paramObj = keys.nextElement();
                Object valueObj = outParams.get(paramObj);
                int order = paramObj != null ? ((Integer) paramObj).intValue() :
                            0;
                int dataType = valueObj != null ? ((Integer) valueObj).intValue() :
                               0;
                if (dataType == java.sql.Types.VARCHAR) {
                    result.add(aCallStmt.getString(order));
                } else if (dataType == java.sql.Types.TIMESTAMP) {
                    result.add(aCallStmt.getTimestamp(order));
                } else if (dataType == java.sql.Types.INTEGER) {
                    result.add(new Integer(aCallStmt.getInt(order)));
                } else if (dataType == java.sql.Types.FLOAT) {
                    result.add(new Float(aCallStmt.getFloat(order)));
                } else if (dataType == oracle.jdbc.OracleTypes.CURSOR) {
                    System.out.println("cursor==============================");
                }
            }
        } catch (SQLException e) {
            //throw new Exception(e);
            e.printStackTrace();
        } finally {
            try { //release resource
                if (rs != null) {
                    rs.close();
                }
                if (aCallStmt != null) {
                    aCallStmt.close();
                    aCallStmt = null;
                }
            } catch (Exception e) {
                throw new Exception(e);
            }

        } //end of finally
        return result;
    } //End - executeStoredProc
}
